﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace ImpOracleFromExcel
{
    public class OleDBHelper
    {
        private FrmMain _app;

        private string _connString;

        public OleDBHelper(string fileName, FrmMain app)
        {
            _app = app;

            try
            {
                FileInfo fi = new FileInfo(fileName);
                if (fi.Extension == ".xls")
                {
                    _connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
                }
                else if (fi.Extension == ".xlsx")
                {
                    _connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0";
                }
                else
                {
                    _app.LogMsg("工具暂不支持该类型的数据源，请检查文件是否正确");
                }

                _app.LogMsg(_connString);
            }
            catch (Exception exc)
            {
                _app.LogMsg(exc.ToString());
            }
        }

        public List<string> GetExcelSheets()
        {
            using (OleDbConnection conn = new OleDbConnection(_connString))
            {
                try
                {
                    conn.Open();
                    _app.LogMsg("数据源文件打开成功.");

                    _app.LogMsg("正在读取Excel中的Sheet页...");
                    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    if (dtSheet != null)
                    {
                        List<string> strs = new List<string>();
                        foreach (DataRow dr in dtSheet.Rows)
                        {
                            try
                            {
                                strs.Add(dr["TABLE_NAME"].ToString());
                            }
                            catch (Exception e)
                            {
                                _app.LogMsg(e.ToString());
                            }
                        }

                        _app.LogMsg("Excel中Sheet页读取成功.");

                        return strs;
                    }
                }
                catch (Exception exc)
                {
                    _app.LogMsg(exc.ToString());
                }
            }

            return null;
        }

        public DataTable GetSheetData(string sql)
        {
            using (OleDbConnection conn = new OleDbConnection(_connString))
            {
                try
                {
                    conn.Open();
                    _app.LogMsg("数据源文件打开成功.");
                    _app.LogMsg(sql);
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
                catch (Exception exc)
                {
                    _app.LogMsg(exc.ToString());
                    _app.LogMsg(sql);
                }
            }

            return null;
        }
    }
}
